using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace MacViet.DataBaseAPI
{	
	/// <summary>
	/// Description: Concrete DataProvider for table Employees. Provides atomic API functions.
	/// Author:  phong.huynh
	/// Created: 8/13/2010 12:08:58 AM
	/// </summary>
	public class EMPLOYEESDataAPI
	{
		private SQLHelper _dbTool = new SQLHelper();
		// ************************************
		// Implements function getByColumnName
		// ************************************

        public DataTable getByEMP_ID(System.Int32 EMP_ID, System.Int32 ACC_ID)
		{
            SqlParameter[] parm = {  new SqlParameter(EMPLOYEESDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(EMPLOYEESDTO._EMP_ID,EMP_ID)};
			return _dbTool.executeReader("SP_SEL_EMPLOYEES_getByEMP_ID", parm);
		}



        public DataTable getByACC_ID(System.Int32 ACC_ID)
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._ACC_ID,ACC_ID)};
			return _dbTool.executeReader("SP_SEL_EMPLOYEES_getByACC_ID", parm);
		}
        public DataTable getEmployeeByAccout(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("sp_get_emp_name", parm);
        }

        public DataTable getEmployee_Report(System.Int32 ACC_ID,System.Int32 emp_id)
        {
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._ACC_ID, ACC_ID),
                                    new SqlParameter(EMPLOYEESDTO._EMP_ID,emp_id)};
            return _dbTool.executeReader("sp_emp_report", parm);
        }
		
		public  DataTable getByEMP_STARTDATE(System.DateTime EMP_STARTDATE)
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_STARTDATE,EMP_STARTDATE)};
			return _dbTool.executeReader("SP_SEL_EMPLOYEES_getByEMP_STARTDATE", EMP_STARTDATE);
		}
		
		public  DataTable getByEMP_LICENSE(System.String EMP_LICENSE)
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_LICENSE,EMP_LICENSE)};
			return _dbTool.executeReader("SP_SEL_EMPLOYEES_getByEMP_LICENSE", parm);
		}
		
		public DataTable getByEMP_POSITION(System.String EMP_POSITION)
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_POSITION,EMP_POSITION)};
			return _dbTool.executeReader("SP_SEL_EMPLOYEES_getByEMP_POSITION", EMP_POSITION);
		}
		
        

		// *************************************************
		// Implement function getAll/getByDynamicCondition
		// *************************************************

		public DataTable getAll()
		{            
			return _dbTool.executeReader("SP_SEL_EMPLOYEES_getAll");
		}
        public DataTable getAllEmployees( System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._ACC_ID, ACC_ID)};
            return _dbTool.executeReader("SP_SEL_getAll_EMPLOYEES", parm);
        } 


		// ********************************************************
		// Implement function getByPrimaryKeys / getByForeignKeys
		// In case table have composite primary/foreign keys
		// ********************************************************

		public DataTable getByPrimaryKeys(System.Int32 EMP_ID,System.Int32 ACC_ID )
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_ID, EMP_ID), 
                                    new SqlParameter(EMPLOYEESDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SEL_EMPLOYEES_getByPrimaryKeys", parm);
		}

        public DataTable getNameEmpID(System.Int32 EMP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_ID, EMP_ID), 
                                    new SqlParameter(EMPLOYEESDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SEL_EMPLOYEES_By_ID", parm);
        }

        public DataTable getAccounts()
        {
            return _dbTool.executeReader("SP_SEL_EMPLOYEES_getAccounts");
        }   

		public  bool insertEMPLOYEES(ref System.Int32 EMP_ID , System.Int32 ACC_ID,
            System.String EMP_FIRSTNAME, System.String EMP_LASTNAME, System.String EMP_ADDRESS, 
            System.String EMP_PHONE, System.DateTime EMP_STARTDATE, System.String EMP_LICENSE, 
            System.String EMP_POSITION, System.Int32 EMP_COMMISSION)
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_ID,1),
                                  new SqlParameter(EMPLOYEESDTO._ACC_ID,ACC_ID),
                                  new SqlParameter(EMPLOYEESDTO._EMP_FIRSTNAME,EMP_FIRSTNAME),
                                  new SqlParameter(EMPLOYEESDTO._EMP_LASTNAME,EMP_LASTNAME),
                                  new SqlParameter(EMPLOYEESDTO._EMP_ADDRESS,EMP_ADDRESS),
                                  new SqlParameter(EMPLOYEESDTO._EMP_PHONE,EMP_PHONE),
                                  new SqlParameter(EMPLOYEESDTO._EMP_STARTDATE,EMP_STARTDATE),
                                  new SqlParameter(EMPLOYEESDTO._EMP_LICENSE,EMP_LICENSE),
                                  new SqlParameter(EMPLOYEESDTO._EMP_POSITION,EMP_POSITION),
                                  new SqlParameter(EMPLOYEESDTO._EMP_COMMISSION, EMP_COMMISSION)};
			int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_EMPLOYEES_values",parm);
			return (effectedRows == 1);
		}    


		///<summary>		
		/// Thu+.c hie^.n ca^u le^.nh update du+.a tre^n kho'a chi'nh
		///</summary>

		public bool updateEMPLOYEES(System.Int32 EMP_ID , System.Int32 ACC_ID, 
            System.String EMP_FIRSTNAME, System.String EMP_LASTNAME, System.String EMP_ADDRESS, 
            System.String EMP_PHONE, System.DateTime EMP_STARTDATE, System.String EMP_LICENSE, 
            System.String EMP_POSITION, System.Int32 EMP_COMMISSION)
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_ID,EMP_ID),
                                  new SqlParameter(EMPLOYEESDTO._ACC_ID,ACC_ID),
                                  new SqlParameter(EMPLOYEESDTO._EMP_FIRSTNAME,EMP_FIRSTNAME),
                                  new SqlParameter(EMPLOYEESDTO._EMP_LASTNAME,EMP_LASTNAME),
                                  new SqlParameter(EMPLOYEESDTO._EMP_ADDRESS,EMP_ADDRESS),
                                  new SqlParameter(EMPLOYEESDTO._EMP_PHONE,EMP_PHONE),
                                  new SqlParameter(EMPLOYEESDTO._EMP_STARTDATE,EMP_STARTDATE),
                                  new SqlParameter(EMPLOYEESDTO._EMP_LICENSE,EMP_LICENSE),
                                  new SqlParameter(EMPLOYEESDTO._EMP_POSITION,EMP_POSITION),
                                  new SqlParameter(EMPLOYEESDTO._EMP_COMMISSION, EMP_COMMISSION)};
			int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_EMPLOYEES_update", parm);

			return affectRows == 1;
		}
        public bool deleteEMPLOYEES(System.Int32 EMP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_ID, EMP_ID)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_DEL_EMPLOYEES_deleteByPK", parm);
            return affectRows == 1;
        }

		
		// ********************************************************************************
		// Implementation of deleteAll/deteleByForeignKeys/deleteByPrimaryKeys function		
		// ********************************************************************************

		// return number of deleted rows		
		public int deleteAll()
		{
			return _dbTool.ExecuteNoneQuery("SP_DEL_EMPLOYEES_deleteAll");
		}

		// return number of deleted rows
		public  int deleteByPrimaryKeys(System.Int32 EMP_ID )
		{
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_ID,EMP_ID)};
			return _dbTool.ExecuteNoneQuery("SP_DEL_EMPLOYEES_deleteByPrimaryKeys",parm);
		}

        public DataTable getACC_IDByPK(System.Int32 EMP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(EMPLOYEESDTO._EMP_ID, EMP_ID)};
            return _dbTool.executeReader("SP_SEL_EMPLOYEES_getACC_IDByPK", parm);
        }

        public DataTable checkEmployee(System.Int32 ACC_ID, System.String checkPhone)
        {
            SqlParameter[] parm = {new SqlParameter(EMPLOYEESDTO._ACC_ID , ACC_ID),
                                    new SqlParameter("@checkPhone", checkPhone)};
            return _dbTool.executeDataTable("SP_SEL_EMPLOYEES_checkEmployee", parm);
        }

		
	}
}